/*****************************************************************************
 *
 *                      HUSONG PROPRIETARY INFORMATION
 *
 *          The information contained herein is proprietary to HUSONG
 *           and shall not be reproduced or disclosed in whole or in part
 *                    or used for any design or manufacture
 *              without direct written authorization from HUSONG.
 *
 *            Copyright (c) 2015 by HUSONG.  All rights reserved.
 *
 *****************************************************************************/
package com.husong.mapper.provider;

import java.math.BigDecimal;
import java.util.Map;

/**
 * @author maxmin
 *
 * @date 2015年11月25日 下午1:44:32
 */
public class OrderProvider {
	/**
	 * 查询经纪人历史订单
	 *
	 * @param params
	 *
	 * @return
	 */
	public String findHistorygOrdesrByBroker(Map<String, Object> params){
		String key=(String)params.get("key");
		StringBuffer sbBuffer=new StringBuffer();
		sbBuffer.append("SELECT o.*,o.typ as type,a1.name as fromName,a1.text as fromAddress,a1.telephone as fromTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",u.username as uName,u.header as uHeader,u.telephone as uTelephone");
		sbBuffer.append(",b.username as bName,b.header as bHeader,b.telephone as bTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",(SELECT GROUP_CONCAT(op.path) FROM hs_order_pic op WHERE op.order_id=o.id ) picpath");
		sbBuffer.append(",(SELECT count(1) FROM hs_evaluate e WHERE e.orderid=o.id and e.userid=o.broker_id ) isEvaluated");
		sbBuffer.append(" FROM hs_order o ");
		sbBuffer.append(" left join hs_address a1 on a1.id=o.address_id");
		sbBuffer.append(" left join hs_address a2 on a2.id=o.to_address_id");
		sbBuffer.append(" left join hs_user u on u.id=o.user_id ");
		sbBuffer.append(" left join hs_user b on b.id=o.broker_id ");
		sbBuffer.append(" LEFT JOIN hs_order_broker ob ");
		sbBuffer.append(" ON  o.id = ob.order_id ");
		sbBuffer.append(" WHERE o.broker_id='"+key+"' AND (order_status_id>7 OR (o.isdelete=1 and order_status_id>2)) ");
		String lastDate=(String)params.get("lastDate");
		Long lastorderid=(Long)params.get("lastorderid");
		if(lastorderid!=null&&lastorderid!=0){
			sbBuffer.append(" AND o.row_id<"+lastorderid+"");
		}
		sbBuffer.append(" AND o.create_date<='"+lastDate+"'");
		sbBuffer.append(" order by o.create_date desc,o.row_id desc limit 10");
		return sbBuffer.toString();
	}
	/**
	 * 查询雇主历史订单
	 *
	 * @param params
	 *
	 * @return
	 */
	public String findHistoryOrdersByUserId(Map<String, Object> params){
		String key=(String)params.get("key");
		StringBuffer sbBuffer=new StringBuffer();
		sbBuffer.append("SELECT o.*,o.typ as type,a1.name as fromName,a1.text as fromAddress,a1.telephone as fromTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",u.username as uName,u.header as uHeader,u.telephone as uTelephone");
		sbBuffer.append(",b.username as bName,b.header as bHeader,b.telephone as bTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",(SELECT GROUP_CONCAT(op.path) FROM hs_order_pic op WHERE op.order_id=o.id ) picpath");
		sbBuffer.append(",(SELECT count(1) FROM hs_evaluate e WHERE e.orderid=o.id and e.userid=o.user_id ) isEvaluated");
		sbBuffer.append(" FROM hs_order o ");
		sbBuffer.append(" left join hs_address a1 on a1.id=o.address_id");
		sbBuffer.append(" left join hs_address a2 on a2.id=o.to_address_id");
		sbBuffer.append(" left join hs_user u on u.id=o.user_id ");
		sbBuffer.append(" left join hs_user b on b.id=o.broker_id ");
		sbBuffer.append(" LEFT JOIN hs_order_broker ob ");
		sbBuffer.append(" ON  o.id = ob.order_id ");
		sbBuffer.append(" WHERE o.user_id='"+key+"' AND (order_status_id>7 OR (o.isdelete=1 and order_status_id>2)) ");
		String lastDate=(String)params.get("lastDate");
		Long lastorderid=(Long)params.get("lastorderid");
		if(lastorderid!=null&&lastorderid!=0){
			sbBuffer.append(" AND o.row_id<"+lastorderid+"");
		}
		sbBuffer.append(" AND o.create_date<='"+lastDate+"'");
		sbBuffer.append(" order by o.create_date desc,o.row_id desc limit 10");
		return sbBuffer.toString();
	}
	
	/**
	 * 查询经纪人任务中
	 *
	 * @param params
	 *
	 * @return
	 */
	public String findTaskingOrdesrByBroker(Map<String, Object> params){
		String key=(String)params.get("key");
		StringBuffer sbBuffer=new StringBuffer();
		sbBuffer.append("SELECT o.*,o.typ as type,a1.name as fromName,a1.text as fromAddress,a1.telephone as fromTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",u.username as uName,u.header as uHeader,u.telephone as uTelephone");
		sbBuffer.append(",b.username as bName,b.header as bHeader,b.telephone as bTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",(SELECT GROUP_CONCAT(op.path) FROM hs_order_pic op WHERE op.order_id=o.id ) picpath");
		sbBuffer.append(" FROM hs_order o ");
		sbBuffer.append(" left join hs_address a1 on a1.id=o.address_id");
		sbBuffer.append(" left join hs_address a2 on a2.id=o.to_address_id");
		sbBuffer.append(" left join hs_user u on u.id=o.user_id ");
		sbBuffer.append(" left join hs_user b on b.id=o.broker_id ");
//		sbBuffer.append(" LEFT JOIN hs_order_broker ob ");
//		sbBuffer.append(" ON  o.id = ob.order_id ");
		sbBuffer.append(" WHERE (o.broker_id='"+key+"' or ((select count(1) from hs_order_broker ob where o.id = ob.order_id and is_checked=1 ) <1) AND (select count(1) from hs_order_broker where o.id=order_id and user_id='"+key+"') >0) AND o.order_status_id<=7 AND o.isdelete=0 ");
		String lastDate=(String)params.get("lastDate");
		Long lastorderid=(Long)params.get("lastorderid");
		if(lastorderid!=null&&lastorderid!=0){
			sbBuffer.append(" AND o.row_id<"+lastorderid+"");
		}
		sbBuffer.append(" AND o.create_date<='"+lastDate+"'");
		sbBuffer.append(" order by o.create_date desc,o.row_id desc limit 10");
		return sbBuffer.toString();
	}
	/**
     * 查询雇主任务中
     *
     * @param params
     *
     * @return
     */
	public String findTaskingOrdersByUserId(Map<String, Object> params){
		String key=(String)params.get("key");
		StringBuffer sbBuffer=new StringBuffer();
		sbBuffer.append("SELECT o.*,o.typ as type,a1.name as fromName,a1.text as fromAddress,a1.telephone as fromTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",u.username as uName,u.header as uHeader,u.telephone as uTelephone");
		sbBuffer.append(",b.username as bName,b.header as bHeader,b.telephone as bTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",(SELECT GROUP_CONCAT(op.path) FROM hs_order_pic op WHERE op.order_id=o.id ) picpath");
		sbBuffer.append(" FROM hs_order o ");
		sbBuffer.append(" left join hs_address a1 on a1.id=o.address_id");
		sbBuffer.append(" left join hs_address a2 on a2.id=o.to_address_id");
		sbBuffer.append(" left join hs_user u on u.id=o.user_id ");
		sbBuffer.append(" left join hs_user b on b.id=o.broker_id ");
		sbBuffer.append(" WHERE o.user_id='"+key+"' AND o.order_status_id<=7 AND o.isdelete=0 ");
		String lastDate=(String)params.get("lastDate");
		Long lastorderid=(Long)params.get("lastorderid");
		if(lastorderid!=null&&lastorderid!=0){
			sbBuffer.append(" AND o.row_id<"+lastorderid+"");
		}
		sbBuffer.append(" AND o.create_date<='"+lastDate+"'");
		sbBuffer.append(" order by o.create_date desc,o.row_id desc limit 10");
		return sbBuffer.toString();
	}
	
	/**
	 * 查询待抢单列表
	 *
	 * @param params
	 *
	 * @return
	 */
	public String findWaitOrders(Map<String, Object> params){
		String key=(String)params.get("key");
		StringBuffer sbBuffer=new StringBuffer();
		sbBuffer.append("SELECT o.*,o.typ as type,a1.name as fromName,a1.text as fromAddress,a1.telephone as fromTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",u.username as uName,u.header as uHeader,u.telephone as uTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",(SELECT GROUP_CONCAT(op.path) FROM hs_order_pic op WHERE op.order_id=o.id ) picpath");
		sbBuffer.append(" FROM hs_order o ");
		sbBuffer.append(" left join hs_address a1 on a1.id=o.address_id");
		sbBuffer.append(" left join hs_address a2 on a2.id=o.to_address_id");
		sbBuffer.append(" left join hs_user u on u.id=o.user_id ");
		sbBuffer.append(" left join hs_user b on b.id=o.broker_id ");
		sbBuffer.append(" LEFT JOIN hs_order_broker ob ");
		sbBuffer.append(" ON  o.id = ob.order_id ");
		sbBuffer.append(" WHERE o.isdelete=0");
		String cityname=(String)params.get("cityname");
		sbBuffer.append(" AND o.cityname='"+cityname+"'");
		sbBuffer.append(" AND o.id not in (select order_id from hs_order_broker where user_id='"+key+"')");
		sbBuffer.append(" AND o.user_id !='"+key+"'");
		Integer type=(Integer)params.get("type");
		if(type!=null&&type!=0){
			sbBuffer.append(" AND o.order_type_id="+type);
		}
		sbBuffer.append(" AND (SELECT COUNT(1) FROM hs_order_broker ob1 WHERE ob1.order_id=o.id AND ob1.is_checked=1)<1 ");
		String lastDate=(String)params.get("lastDate");
		Long lastorderid=(Long)params.get("lastorderid");
		if(lastorderid!=null&&lastorderid!=0){
			sbBuffer.append(" AND o.row_id<"+lastorderid+"");
		}
		sbBuffer.append(" AND o.create_date<='"+lastDate+"'");
		sbBuffer.append(" order by o.create_date desc,o.row_id desc limit 10");
		return sbBuffer.toString();
	}
	/**
	 * 查询首页呼单列表
	 *
	 * @param params
	 *
	 * @return
	 */
	public String findIndexOrders(Map<String, Object> params){
		StringBuffer sbBuffer=new StringBuffer();
		sbBuffer.append("SELECT o.*,o.`desc`,o.typ as type,a1.name as fromName,a1.text as fromAddress,a1.telephone as fromTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",u.username as uName,u.header as uHeader,u.telephone as uTelephone");
		sbBuffer.append(",a2.text as toAddress,a2.name as toName,a2.telephone as toTelephone");
		sbBuffer.append(",(SELECT GROUP_CONCAT(op.path) FROM hs_order_pic op WHERE op.order_id=o.id ) picpath");
		sbBuffer.append(",(select count(1) from hs_zan z where z.order_id=o.id and status=1) zCount");
		sbBuffer.append(",(select count(1) from hs_comment c where c.order_id=o.id) eCount");
		sbBuffer.append(" FROM hs_order o ");
		sbBuffer.append(" left join hs_address a1 on a1.id=o.address_id");
		sbBuffer.append(" left join hs_address a2 on a2.id=o.to_address_id");
		sbBuffer.append(" left join hs_user u on u.id=o.user_id ");
		sbBuffer.append(" left join hs_user b on b.id=o.broker_id ");
		sbBuffer.append(" LEFT JOIN hs_order_broker ob ");
		sbBuffer.append(" ON  o.id = ob.order_id ");
		sbBuffer.append(" WHERE o.isdelete=0");
		String cityname=(String)params.get("cityname");
		sbBuffer.append(" AND o.cityname='"+cityname+"'");
		Integer type=(Integer)params.get("type");
		if(type!=null){
			sbBuffer.append(" AND o.order_type_id="+type);
		}
//		String lastDate=(String)params.get("lastDate");
		Long lastorderid=(Long)params.get("lastorderid");
		if(lastorderid!=null&&lastorderid!=0){
			sbBuffer.append(" AND o.row_id<"+lastorderid+"");
		}
//		sbBuffer.append(" AND o.create_date<='"+lastDate+"'");
		sbBuffer.append(" order by o.row_id desc limit 10");
		return sbBuffer.toString();
	}
	
	/**
	 * 选择经纪人列表
	 * @param params
	 * @return
	 */
	public String chooseBrokerList(Map<String, Object> params){
		String orderid=(String)params.get("orderid");
		Integer type=(Integer)params.get("type");
		StringBuffer sbBuffer=new StringBuffer();
		sbBuffer.append("select ob.*,ifnull((select SUM(e.point)/COUNT(e.point) from hs_evaluate e where e.userid=ob.user_id),0) as stars,");
		sbBuffer.append(" u.declaration,u.header,u.username,u.point,");
		sbBuffer.append(" (ROUND(2*ASIN(SQRT(POW(SIN((ob.lat*PI()/180-o.lat*PI()/180)/2),2)+COS(ob.lat*PI()/180)*COS(o.lat*PI()/180)*POW(((ob.lng*PI()/180-o.lng*PI()/180)/2),2)))*6378137.0*10000)/10000) distance"); 
		sbBuffer.append(" from hs_order_broker ob ");
		sbBuffer.append(" LEFT JOIN hs_order o on o.id=ob.order_id ");
		sbBuffer.append(" LEFT JOIN hs_user u on u.id=ob.user_id");
		sbBuffer.append(" LEFT JOIN hs_broker b on b.user_id=ob.user_id");
		sbBuffer.append(" WHERE ob.order_id = '"+orderid+"'");
		if(type!=null&&type==1){
			sbBuffer.append("ORDER BY stars desc,u.point desc,distance desc");
		}else if(type!=null&&type==2){
			sbBuffer.append("ORDER BY distance desc");
		}else if(type!=null&&type==3){
			sbBuffer.append("ORDER BY stars desc");
		}else if(type!=null&&type==4){
			sbBuffer.append("ORDER BY u.point desc");
		}
		return sbBuffer.toString();
	}
	
	public String updateOrderFee(Map<String, Object> params){
		StringBuffer sb=new StringBuffer();
		Integer status=(Integer)params.get("status");
		Integer oriStatus=(Integer)params.get("oriStatus");
		Integer adverttype=(Integer)params.get("adverttype");
		Integer advertId=(Integer)params.get("advertId");
		BigDecimal fee=(BigDecimal)params.get("fee");
		String orderid=(String) params.get("orderid");
		sb.append("UPDATE hs_order SET order_status_id="+status);
		if (advertId==0) {
			sb.append(",fee=").append(fee);
		}else {
			if (adverttype==3) {
				sb.append(",fee=").append(fee);
			}
		}
		sb.append(" WHERE id='"+orderid+"' and order_status_id="+oriStatus);
		
		
		return sb.toString();
	}
	
	
	
	public String getBrokerToMeEvalustes(Map<String,Object> params){
		String user_id=(String)params.get("user_id");
		String lastDate=(String)params.get("lastDate");
		Long lastrowid=(Long)params.get("lastrowid");
		StringBuffer sb=new StringBuffer();
		sb.append("select e.*,u.username,u.header from hs_evaluate e join hs_order o on o.broker_id=e.userid and "
				+ " o.id= e.orderid "
				+ " left join hs_user u on "
				+ " u.id=o.broker_id where " + " o.user_id= '"+user_id+"' ");
		if(lastrowid!=null&&lastrowid!=0){
			sb.append(" AND e.row_id<"+lastrowid+"");
		}
		sb.append(" AND e.create_date<='"+lastDate+"'");
		sb.append(" order by e.create_date desc,e.row_id desc  limit 10");
		return sb.toString();
    }
    
	public String getUserToMeEvalustes(Map<String,Object> params){
		String user_id=(String)params.get("user_id");
		String lastDate=(String)params.get("lastDate");
		Long lastrowid=(Long)params.get("lastrowid");
		StringBuffer sb=new StringBuffer();
		sb.append("select e.*,u.username,u.header from hs_evaluate e join hs_order o on o.user_id=e.userid and "
				+ " o.id= e.orderid "
				+ " left join hs_user u on "
				+ " u.id=o.user_id  where " + " o.broker_id= '"+user_id+"' ");
		if(lastrowid!=null&&lastrowid!=0){
			sb.append(" AND e.row_id<"+lastrowid+"");
		}
		sb.append(" AND e.create_date<='"+lastDate+"'");
		sb.append(" order by e.create_date desc,e.row_id desc limit 10 ");
		return sb.toString();
		
    }
	
	public String getToMeEvalustes(Map<String,Object> params){
		String user_id=(String)params.get("user_id");
		String lastDate=(String)params.get("lastDate");
		Long lastrowid=(Long)params.get("lastrowid");
		StringBuffer sb=new StringBuffer();
		sb.append("select e.*,u.username,u.header from hs_evaluate e join hs_order o on  "
				+ " o.id= e.orderid "
				+ " left join hs_user u on "
				+ " u.id=o.user_id  where " + " o.broker_id= '"+user_id+"' or o.user_id=' "+user_id+"'");
		if(lastrowid!=null&&lastrowid!=0){
			sb.append(" AND e.row_id<"+lastrowid+"");
		}
		sb.append(" AND e.create_date<='"+lastDate+"'");
		sb.append(" order by e.create_date desc,e.row_id desc limit 10 ");
		return sb.toString();
		
	}
    
	public String getMeToBrokerEvalustes(Map<String,Object> params){
		String user_id=(String)params.get("user_id");
		String lastDate=(String)params.get("lastDate");
		Long lastrowid=(Long)params.get("lastrowid");
		StringBuffer sb=new StringBuffer();
		sb.append("select e.*,u.username,u.header from hs_evaluate e join hs_order o on o.user_id=e.userid and "
				+ " o.id= e.orderid "
				+ " left join hs_user u on "
				+ " u.id=o.broker_id  where "+ " e.userid= '"+user_id+"' ");
		if(lastrowid!=null&&lastrowid!=0){
			sb.append(" AND e.row_id<"+lastrowid+"");
		}
		sb.append(" AND e.create_date<='"+lastDate+"'");
		sb.append(" order by e.create_date desc,e.row_id desc limit 10");
		return sb.toString();
    }
    
	public String getMeToUserEvalustes(Map<String,Object> params){
		String user_id=(String)params.get("user_id");
		String lastDate=(String)params.get("lastDate");
		Long lastrowid=(Long)params.get("lastrowid");
		StringBuffer sb=new StringBuffer();
		sb.append("select e.*,u.username,u.header from hs_evaluate e join hs_order o on o.broker_id=e.userid and "
				+ " o.id= e.orderid "
				+ " left join hs_user u on "
				+ " u.id=o.user_id where "+ " e.userid= '"+user_id+"' ");
		if(lastrowid!=null&&lastrowid!=0){
			sb.append(" AND e.row_id<"+lastrowid+"");
		}
		sb.append(" AND e.create_date<='"+lastDate+"'");
		sb.append(" order by e.create_date desc,e.row_id desc limit 10");
		return sb.toString();
    }
	
	public String getComments(Map<String, Object> params){
		String orderid=(String)params.get("orderid");
		String lastDate=(String)params.get("lastDate");
		Long lastrowid=(Long)params.get("lastrowid");
		StringBuffer sb=new StringBuffer();
		sb.append("select c.*,u.username,u.header,pu.username as parent_username from hs_comment c left join hs_comment p on p.id=c.parent_comment_id left join hs_user u on "
				+ " u.id=c.user_id left join hs_user pu on pu.id=p.user_id where c.order_id= '"+orderid+"'");
		if(lastrowid!=null&&lastrowid!=0){
			sb.append(" AND c.row_id<"+lastrowid+"");
		}
		sb.append(" AND c.create_date<='"+lastDate+"'");
		sb.append(" order by c.create_date desc,c.row_id desc limit 10");
		return sb.toString();
	}
	
	public String getMessages(Map<String, Object> params){
		String userid=(String)params.get("userid");
		int type=(int)params.get("type");
		String lastDate=(String)params.get("lastDate");
		Long lastrowid=(Long)params.get("lastrowid");
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT DISTINCT m.message_id messageId,m.message,m.createtime,m.type,m.orderid,u.header FROM hs_message m  left join hs_user u on u.id=m.from_user_id "
			+ "WHERE m.user_id='"+userid+"'   ");
		if(type==1){
			sb.append("AND m.type=1");
		}else{
			sb.append("AND m.type>1");
		}
		if(lastrowid!=null&&lastrowid!=0){
			sb.append(" AND m.message_id<"+lastrowid+"");
		}
		sb.append(" AND m.createtime<='"+lastDate+"'");
		sb.append(" order by m.createtime desc,m.message_id desc limit 10");
		return sb.toString();
	}
	
	
}
